<?php
namespace App\Models;
use App\Models\BaseModel;
use pdo;
class OperaModel extends BaseModel{
	public function announcemsg($id){
		$sql=sprintf("SELECT * from announce where id=$id");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function announceedit($id,$announcename,$announcecontxt){
		$sql=sprintf("UPDATE announce SET announcename='" .$announcename. "',announcecontxt='" .$announcecontxt. "' WHERE id='" .$id. "'");
		return $this -> query($sql);
	}
	public function announceadd($announcename,$announcecontxt,$time,$riqi,$man){
		$sql = sprintf("insert into announce(announcename,announcecontxt,announcetime,announcedate,announceman) values('$announcename','$announcecontxt','$time','$riqi','$man')");
		return $this -> query($sql);
	}
	public function announcesearch($postTime){
		$sql =sprintf("SELECT * from announce where announcedate='" .$postTime. "'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function searchflower($flowername){
		$sql=sprintf("SELECT * FROM flower where flowername like '".$flowername."'");
	    $sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function flowermsg($id){
		$sql=sprintf("SELECT * FROM flower WHERE id='".$id."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetch();
	}
	public function delstock($id){
		$sql=sprintf("DELETE  FROM flowerstock WHERE flowerid='".$id."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function delstocks($id){
		$sql=sprintf("DELETE  FROM flowerstock WHERE flowerid in ($id)");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function stockmsg($id){
		$sql=sprintf("SELECT * FROM flowerstock WHERE flowerid='".$id."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetch();
	}
	public function searchstock($flowername){
		$sql=sprintf("SELECT * from flowerstock where flowername='".$flowername."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function orderunfinish($postTime){
		$sql=sprintf("SELECT * FROM `order` WHERE orderdate='".$postTime."' and state='未支付'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function orderfinished($postTime){
		$sql=sprintf("SELECT * FROM `order` WHERE orderdate='".$postTime."' and state='已完成'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function  distributeunfinish($ordernumber){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE orderednumber='".$ordernumber."' AND distributed.state='未配送' AND `order`.state='已完成'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function  distributedoing($ordernumber){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE orderednumber='".$ordernumber."' AND distributed.state='正配送' AND `order`.state='已完成'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function  distributefinished($ordernumber){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE orderednumber='".$ordernumber."' AND distributed.state='已配送' AND `order`.state='已完成'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function getlunbo($listname){
		$sql=sprintf("SELECT * FROM lunbolist WHERE title='".$listname."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function getxianshi($listname){
		$sql=sprintf("SELECT * FROM xianshi WHERE title='".$listname."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function getcustomer($email){
		$sql=sprintf("SELECT * FROM customer WHERE email='".$email."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function getemployee($username){
		$sql=sprintf("SELECT * FROM employ LEFT JOIN role on employ.roleid=role.id WHERE username='".$username."'");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function getSystem($opername){
		$sql=sprintf("SELECT * FROM logbook WHERE operaname='".$opername."' and ctype=1");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function getBusiness($opername){
		$sql=sprintf("SELECT * FROM logbook WHERE operaname='".$opername."' and ctype=2");
		$sth = $this -> _dbHandle -> prepare($sql);
		$sth -> execute();
		return $sth -> fetchAll();
	}
	public function customermsg($id){
		$sql=sprintf("SELECT * FROM customer WHERE id='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function customeredit($id,$username,$email,$phone){
		$sql=sprintf("UPDATE customer SET username='" .$username. "',email='" .$email. "',phone='".$phone."' WHERE id='" .$id. "'");
		return $this -> query($sql);
	}
	public function employeeMsg($id){
		$sql=sprintf("SELECT * FROM employ LEFT JOIN role on employ.roleid=role.id WHERE employ.id='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function employedit($id,$username,$realname,$roleid,$phone,$email){
		$sql=sprintf("UPDATE employ SET username='" .$username. "',email='" .$email. "',phone='".$phone."' ,realname='".$realname."', roleid='".$roleid."'  WHERE id='" .$id. "'");
		return $this -> query($sql);
	}
	public function employadd($username,$realname,$roleid,$phone,$email){
		$sql = sprintf("insert into employ(username,realname,password,roleid,phone,email) values('$username','$realname','123456','$roleid','$phone','$email')");
		return $this -> query($sql);
	}
	public function delemploy($id){
		$sql=sprintf("DELETE  FROM employ WHERE id='".$id."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function delemploys($id){
		$sql=sprintf("DELETE  FROM employ WHERE id in ($id)");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function rolemsg($id){
		$sql=sprintf("SELECT * FROM role WHERE id='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function roleedit($id,$rolecode,$rolename,$rolediscript,$rolelv){
		$sql=sprintf("UPDATE role SET rolecode='" .$rolecode. "',rolename='" .$rolename. "',rolediscript='".$rolediscript."' ,rolelv='".$rolelv."'  WHERE id='" .$id. "'");
		return $this -> query($sql);
	}
	public function roleadd($rolecode,$rolename,$rolediscript,$rolelv){
		$sql = sprintf("insert into role(rolename,rolecode,rolediscript,rolelv) values('$rolename','$rolecode','$rolediscript','$rolelv')");
		return $this -> query($sql);
	}
	public function roledelect($id){
		$sql=sprintf("DELETE  FROM role WHERE id='".$id."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function stockedit($id,$flowername,$flowerprice,$flowersize,$flowerproduct,$flowertype,$flowerinprice,$flowerupstock,$flowerdownstock,$supplier,$count){
		$sql=sprintf("UPDATE flowerstock SET flowername='" .$flowername. "',price='".$flowerprice."',flowersize='".$flowersize."',flowerproduct='".$flowerproduct."',flowertype='".$flowertype."',flowerinprice='".$flowerinprice."',flowerupstock='".$flowerupstock."',flowerdownstock='".$flowerdownstock."',supplier='".$supplier."',count='".$count."'  WHERE flowerid='" .$id. "'");
		return $this -> query($sql);
	}
	public function stockadd($flowername,$flowerprice,$flowersize,$flowerproduct,$flowertype,$flowerinprice,$flowerupstock,$flowerdownstock,$supplier,$count){
		$sql = sprintf("insert into flowerstock(flowername,price,flowersize,flowerproduct,flowertype,flowerinprice,flowerupstock,flowerdownstock,supplier,count) values('$flowername','$flowerprice','$flowersize','$flowerproduct','$flowertype','$flowerinprice','$flowerupstock','$flowerdownstock','$supplier','$count')");
		return $this -> query($sql);
	}
	public function ordermsg($id){
		$sql=sprintf("SELECT * FROM `order` where id='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function orderedit($id,$orderman,$ordernumber,$ordercontxt,$ordertime,$orderdate,$state){
		$sql=sprintf("UPDATE `order` SET orderman='".$orderman."',ordernumber='".$ordernumber."', ordercontxt='".$ordercontxt."',ordertime='".$ordertime."',orderdate='".$orderdate."',state='".$state."' WHERE id='".$id."'");
		return $this -> query($sql);
	}
	public function distributemsg($id){
		$sql=sprintf("SELECT * FROM distributed LEFT JOIN `order` ON distributed.orderednumber=`order`.ordernumber WHERE distributeid='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function showedit($id,$flowername,$flowerprice,$love,$sold,$img){
		$sql=sprintf("UPDATE flower SET flowername='".$flowername."',flowerprice='".$flowerprice."',love='".$love."',sold='".$sold."',img64='".$img."' WHERE id='".$id."'");
		return $this -> query($sql);
	}
	public function lunbomsg($id){
		$sql=sprintf("SELECT * FROM lunbolist WHERE id='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function lunboedit($id,$img,$title){
		$sql=sprintf("UPDATE `lunbolist` SET img64='".$img."',title='".$title."' WHERE id='".$id."'");
		return $this -> query($sql);
	}
	public function xianshimsg($id){
		$sql=sprintf("SELECT * FROM xianshi WHERE id='".$id."'");
		$sth=$this->_dbHandle->prepare($sql);
	    $sth -> execute();
        return $sth -> fetch();
	}
	public function xianshiedit($id,$img,$title){
		$sql=sprintf("UPDATE `xianshi` SET img64='".$img."',title='".$title."' WHERE id='".$id."'");
		return $this -> query($sql);
	}
	public function lunbodel($id){
		$sql=sprintf("DELETE  FROM lunbolist WHERE id='".$id."'");
		$sth =$this -> _dbHandle -> prepare($sql);
	    $sth -> execute();
        return $sth -> rowCount();
	}
	public function lunboadd($img,$title){
		$sql = sprintf("insert into lunbolist(title,img64) values('$title','$img')");
		return $this -> query($sql);
	}
}
?>